package com.qf.index;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.alibaba.fastjson.JSONObject;

import java.io.File;
import java.sql.SQLException;
import java.util.*;

public class xxmuStemCell {

    public static void main(String[] args) throws SQLException {
        String newDataName = "scholar_25_02.";
        String jourMergeTable = newDataName + "basic_jour_merge";
        String ncTextTable = "html.nc_text_table_0320";
        String ncbiJournalPdfTable = "dir.ncbijournalpdf_20250415_fullpath";
        String fullPathPmIdTable = "dir.xxmu_full_path_pmid_year";

        Set<String> ncNlmIdSet = getNcNlmIdSet(jourMergeTable, "id", "zky_dalei`,`nc_nlmid_list");
        System.out.println("ncNlmIdSet.size(): " + ncNlmIdSet.size());

        Map<Integer, Integer> pmIdAndYearMap = getPmIdAndYearMap(ncTextTable, "id", "jid`,`pmid", ncNlmIdSet);
        System.out.println("pmIdAndYearMap.size(): " + pmIdAndYearMap.size());

        insertTable(ncbiJournalPdfTable, "pid", "fullpath", pmIdAndYearMap, fullPathPmIdTable);

        //pmIdAndYearMap.size(): -- 52434
        //pmIdAndYearMap.size(): 22325
        //2016 -- 1689
        //2017 -- 1857
        //2018 -- 1827
        //2019 -- 1979
        //2020 -- 2190
        //2021 -- 2458
        //2022 -- 2157
        //2023 -- 2017
        //2024 -- 2180
        //2025 -- 697
        //2014 -- 1398
        //2015 -- 1545

//pmIdAndYearMap.size(): -- 52434
//pmIdAndYearMap.size(): 9019
//2016 -- 702
//2017 -- 769
//2018 -- 778
//2019 -- 785
//2020 -- 790
//2021 -- 833
//2022 -- 744
//2023 -- 767
//2024 -- 815
//2025 -- 385
//2014 -- 702
//2015 -- 687


    }

    public static Set<String> getNcNlmIdSet(String jourMergeTable, String autoId, String field) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + jourMergeTable);
        Set<String> ncNlmIdSet = new HashSet<>();
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jourMergeTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jourMergeTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    int zkyDaLei = Utils.getIntField(entry, "zky_dalei");
                    if (zkyDaLei == 1 || zkyDaLei == 2) {
                        String ncNlmIds = Utils.getStrField(entry, "nc_nlmid_list");
                        if (ncNlmIds.contains("\"]")) {
                            List<String> ncNlmIdList = JSONObject.parseArray(ncNlmIds.toUpperCase(), String.class);
                            ncNlmIdSet.addAll(ncNlmIdList);
                        }
                    }
                }
            }
        }
        return ncNlmIdSet;
    }

    public static Map<Integer, Integer> getPmIdAndYearMap(String ncTextTable, String autoId, String field, Set<String> ncNlmIdSet) throws SQLException {
        List<File> files = FileUtil.loopFiles("C:\\临时文件\\2025_03\\新乡干细胞");
        Map<Integer, Integer> pmIdAndYearMap = new HashMap<>();
        for (File file : files) {
            String filePath = file.getAbsolutePath();
            String[] split = filePath.replace("C:\\临时文件\\2025_03\\新乡干细胞\\", "").split("_");
            System.out.println("filePath: " + filePath);
            if (split.length == 2 && NumberUtil.isInteger(split[0]) && filePath.endsWith(".txt")) {
                int year = Integer.parseInt(split[0]);
                List<String> list = FileUtil.readLines(filePath, "utf-8");
                for (String s : list) {
                    if (s.length() > 5 && NumberUtil.isInteger(s)) {
                        pmIdAndYearMap.put(Integer.parseInt(s), year);
                    }
                }
            }
        }
        System.out.println("pmIdAndYearMap.size(): -- " + pmIdAndYearMap.size());
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + ncTextTable);
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + ncTextTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + ncTextTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String nlmId = Utils.getStrField(entry, "jid").toUpperCase();
                    if (!ncNlmIdSet.contains(nlmId)) {
                        int pmId = Utils.getIntField(entry, "pmid");
                        pmIdAndYearMap.remove(pmId);
                    }
                }
            }
        }
        return pmIdAndYearMap;
    }

    public static void insertTable(String ncbiJournalPdfTable, String autoId, String field, Map<Integer, Integer> pmIdAndYearMap, String fullPathPmIdTable) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + ncbiJournalPdfTable);
        Map<Integer, Set<Integer>> yearAndPmIdSetMap = new HashMap<>();
        Db.use().execute("truncate table " + fullPathPmIdTable);
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + ncbiJournalPdfTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + ncbiJournalPdfTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String fullPath = Utils.getStrField(entry, "fullpath");
                    String fileId = Utils.getFileId(fullPath);
                    if (fileId.startsWith("PM") && NumberUtil.isInteger(fileId.substring(2))) {
                        int pmId = Integer.parseInt(fileId.substring(2));
                        if (pmIdAndYearMap.containsKey(pmId)) {
                            Integer year = pmIdAndYearMap.get(pmId);
                            try {
                                Db.use().insert(Entity.create(fullPathPmIdTable).set("full_path", fullPath).set("pmId", pmId).set("year", year));
                                Utils.putIntSetIntMap(yearAndPmIdSetMap, year, pmId);
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
        for (Map.Entry<Integer, Set<Integer>> entry : yearAndPmIdSetMap.entrySet()) {
            System.out.println(entry.getKey() + " -- " + entry.getValue().size());
        }
    }


}
